package chh3933.generator;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DoSqlExe {
    private static String parenId=null , nowDate=null,
        CjglyId="1";//超级管理员角色id
    public final static SimpleDateFormat DateFormat_yyyy_MM_ddHHmmss = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    public static void update(String sql,Connection connection) throws Exception{
        Statement statement = null;
        try {
            //1.0获取数据库的连接 connection = getConnection();
            //2.0调用Connection 对象的createStatement()方法
            statement = connection.createStatement();
            //3.0发送SQL语句：调用exexuteUpdate()方法
            statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //statement.close();
            //connection.close();
        }
    }

    private static Connection connection;
    public static void main(String[] args) throws SQLException {
        try {
            openDatabaseConnection();
            //插入数据
            executeUpdate("INSERT INTO `test_user` VALUES (NULL, 2, '称呼', NULL, 1.24, '1999-10-11', '2023-02-11 16:54:16', 1, 0);");
            //插入数据结束
        } finally {
            closeDatabaseConnection();
        }
    }
    public static String format_yyyy_MM_ddHHmmss(Date date){
        return DateFormat_yyyy_MM_ddHHmmss.format(date);
    }
    //executeUpdate（）方法具体实现
    private static Long executeUpdate(String sql) throws SQLException {
        //System.out.println("Create data: " );
        try{
            // 指定返回生成的主键
            PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // 如果使用静态的SQL，则不需要动态插入参数
            //pstmt.setString(1, new Date().toLocaleString());
            pstmt.executeUpdate();
            // 检索由于执行此 Statement 对象而创建的所有自动生成的键
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                Long id = rs.getLong(1);
                //System.out.println("数据主键：" + id);
                return id;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    //executeUpdateNoKey（）方法具体实现
    private static Long executeUpdateNoKey(String sql) throws SQLException {
        try{
            connection.prepareStatement(sql).executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    //executeQuery（）方法具体实现
    public static ResultSet executeQuery(String sql) throws SQLException {
        //System.out.println("Create data: " );
        try{
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            //System.out.println("Rows executeQuery: "+rs.getMetaData().getColumnCount());
            return rs;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public static String getPermissionId(String sql) throws SQLException {
        try{
            openDatabaseConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            if(rs.next()){
                return rs.getString("id");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeDatabaseConnection();
        }
        return null;
    }

    public static boolean isExist(String title)throws SQLException {
        parenId = getPermissionId("SELECT * FROM sys_permission WHERE title='"+title+"'");
        nowDate = format_yyyy_MM_ddHHmmss(new Date());
        if(parenId==null)return false;
        return true;
    }
    //权限编码
    private static boolean isExistCt(String code)throws SQLException {
        try{
            PreparedStatement statement = connection.prepareStatement("SELECT * FROM sys_permission WHERE code='"+code+"'");
            ResultSet rs = statement.executeQuery();
            if(rs.next()){
                if(rs.getString("id")!=null)
                    return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
        }
        return false;
    }
    //所属企业id == 1
    private static final String INSERTper = "INSERT INTO sys_permission(sys_branch_office_id,parent_id,title,code,path,name,component,order_num,sort,type,create_time,update_time,hidden,hidden_breadcrumb) VALUES (1,",
            INSERTro = "INSERT INTO sys_role_permission(sys_branch_office_id,role_Id,menu_id) VALUES (1,";
    //执行创建
    public static void doCreatePer(String parentName,String perName,String entityPath)throws SQLException{
        try{
            openDatabaseConnection();
            String code = MyGeneratorConfig.ModuleName+":"+entityPath, sql=null;
            Long nowId = null;
            String newCode = code+":list";
            if(!isExistCt(newCode)){//sys:role  类型（menu：菜单，iframe：嵌入，link：外链  button）
                sql = INSERTper +parenId+",'"+perName+"','"+newCode+"','/"+entityPath+"List','"+entityPath+"','"+
                        MyGeneratorConfig.ModuleName+"/"+entityPath+"List',1,1,'menu','"+nowDate+"','"+nowDate+"',0,0);";//类型(0目录 1菜单，2按钮)
                nowId = executeUpdate(sql);
                sql = INSERTro+CjglyId+", "+nowId+");";
                executeUpdateNoKey(sql);
                System.out.println("生成列表权限, 并添加角色权限===========" );
            }
            newCode = code+":add";
            if(!isExistCt(newCode) && nowId!=null){
                sql = INSERTper +nowId+",'新增','"+newCode+"','','','',1,0,'button','"+nowDate+"','"+nowDate+"',0,0);";//类型(0目录 1菜单，2按钮)
                Long tpId=executeUpdate(sql);
                sql = INSERTro+CjglyId+", "+tpId+");";
                executeUpdateNoKey(sql);
                System.out.println("生成新增权限, 并添加角色权限===========" );
            }
            newCode = code+":del";
            if(!isExistCt(newCode) && nowId!=null){
                sql = INSERTper +nowId+",'删除','"+newCode+"','','','',1,0,'button','"+nowDate+"','"+nowDate+"',0,0);";
                Long tpId=executeUpdate(sql);
                sql = INSERTro+CjglyId+", "+tpId+");";
                executeUpdateNoKey(sql);
                System.out.println("生成删除权限, 并添加角色权限===========" );
            }
            newCode = code+":edit";
            if(!isExistCt(newCode) && nowId!=null){
                sql = INSERTper +nowId+",'编辑','"+newCode+"','','','',1,0,'button','"+nowDate+"','"+nowDate+"',0,0);";//类型(0目录 1菜单，2按钮)
                Long tpId=executeUpdate(sql);
                sql = INSERTro+CjglyId+", "+tpId+");";
                executeUpdateNoKey(sql);
                System.out.println("生成编辑权限, 并添加角色权限===========" );
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeDatabaseConnection();
        }
    }
    private static void openDatabaseConnection() throws SQLException{
        //System.out.println("Opening database connection...");
        connection = DriverManager.getConnection(
                MyGeneratorConfig.JdbcUrlSys,//"jdbc:mariadb://localhost:3306/jdbc_demo",
                MyGeneratorConfig.DbName,//"user",
                MyGeneratorConfig.DbPWD//"password"
        );
        //System.out.println("Connection valid: " + connection.isValid(5));
    }
    private static void closeDatabaseConnection() throws SQLException {
        connection.close();
        //System.out.println("Connection valid: " + connection.isValid(5));
    }
}
